import pymysql
import time

db = pymysql.connect
cursor = pymysql.connect.cursor


def MySQL_Init():
    global db, cursor
    MySQLConfig = open('MySQL_config.txt', encoding='utf-8')
    configs = MySQLConfig.readline()
    MySQLConfig.close()
    if not configs:
        set_MySQL_config()
    MySQLConfig = open('MySQL_config.txt', encoding='utf-8')
    configs = MySQLConfig.readline()
    MySQLConfig.close()
    configs = eval(configs)
    # print(configs)
    try:  # 尝试连接数据库
        db = pymysql.connect(host=configs['host'],
                             user=configs['user'],
                             password=configs['pwd'],
                             db=configs['dbName'])
    except pymysql.err.OperationalError as info:
        print('连接数据库失败,原因:', info)
        exit(0)

    cursor = db.cursor()
    sql = 'SELECT * FROM events limit 1'
    try:  # 查看是否有表
        cursor.execute(sql)
        cursor.fetchone()
    except pymysql.err.ProgrammingError:
        init_tables()
    return 1


def set_MySQL_config():  # 设置数据库连接参数
    MySQLConfig = open('MySQL_config.txt', 'w', encoding='utf-8')
    host = input('请输入host:')
    user = input('请输入user:')
    pwd = input('请输入password:')
    dbName = input('请输入database:')
    configs = {'host': host, 'user': user, 'pwd': pwd, 'db': dbName}
    MySQLConfig.write(str(configs))
    MySQLConfig.close()


def init_tables():  # 初始化表
    sql = 'CREATE TABLE events(' \
          'id INT NOT NULL AUTO_INCREMENT,' \
          'name CHAR(255) NOT NULL,' \
          'mode INT NOT NULL,' \
          'PRIMARY KEY (id)' \
          ')'
    # mode: 0普通，即默认未打卡；1特殊，即默认打卡
    cursor.execute(sql)
    sql = 'CREATE TABLE data(' \
          'id INT NOT NULL AUTO_INCREMENT,' \
          'event_id INT NOT NULL,' \
          'checked INT NOT NULL,' \
          'date DATE NOT NULL,' \
          'note VARCHAR(255),' \
          'PRIMARY KEY (id)' \
          ')'
    cursor.execute(sql)


def close_DB():
    global db, cursor
    cursor.close()
    db.close()


def add_event(name, mode):
    flag = True
    sql = 'INSERT INTO events(name,mode) VALUES (\'{}\',{})'.format(str(name), int(mode))
    try:
        cursor.execute(sql)
        db.commit()
    except pymysql.err.ProgrammingError:
        db.rollback()
        flag = False
    return flag


def del_event(event_id):
    flag = True
    sql = 'DELETE FROM events WHERE id={}'.format(int(event_id))
    try:
        cursor.execute(sql)
        db.commit()
    except pymysql.err.ProgrammingError:
        db.rollback()
        flag = False
    return flag


def get_now_time():
    now = time.localtime()
    now = time.strftime('%Y-%m-%d', now)
    return now


def check_data():  # 查看今天是否有记录了
    now_time = get_now_time()
    sql = 'SELECT id,mode FROM events'
    cursor.execute(sql)
    events_id = cursor.fetchall()
    for event in events_id:
        sql = 'SELECT * FROM data WHERE event_id={} AND date=\'{}\''.format(int(event[0]), now_time)
        cursor.execute(sql)
        result = cursor.fetchone()
        if result is None:
            checked = 0
            if event[1] == 1:
                checked = 1
            add_data(event[0], checked, now_time, '')


def add_data(event_id, checked, date, note):
    flag = True
    sql = 'INSERT INTO data(event_id,checked,date,note) VALUES ({},{},\'{}\',\'{}\')'.format(
        int(event_id), int(checked), date, note
    )
    try:
        cursor.execute(sql)
        db.commit()
    except pymysql.err.ProgrammingError:
        db.rollback()
        flag = False
    return flag


def change_data(event_id, date, checked, note):
    flag = True
    sql = 'UPDATE data SET checked={}, note=\'{}\' WHERE event_id={} AND date=\'{}\''.format(
        int(checked), note, int(event_id), date
    )
    try:
        cursor.execute(sql)
        db.commit()
    except pymysql.err.ProgrammingError:
        db.rollback()
        flag = False
    return flag


class EVENTS:
    event_id = 0
    name = ''
    mode = 0
    checked = 0
    note = ''

    def __init__(self, event_id, name, mode):
        self.event_id = event_id
        self.name = name
        self.mode = mode

    def change_data(self, checked, note):
        if change_data(self.event_id, get_now_time(), checked, note):
            self.checked = checked
            self.note = note
            return 1
        else:
            return 0


def get_EVENTS():
    events = {}
    sql = 'SELECT id,name,mode FROM events'
    cursor.execute(sql)
    result = cursor.fetchall()
    for i in result:
        events[int(i[0])] = EVENTS(int(i[0]), i[1], int(i[2]))
    now_time = get_now_time()
    sql = 'SELECT event_id,checked,note FROM data WHERE date=\'{}\''.format(now_time)
    cursor.execute(sql)
    result = cursor.fetchall()
    for i in result:
        if int(i[0]) in events.keys():
            events[int(i[0])].checked = int(i[1])
            events[int(i[0])].note = i[2]
    return events


if __name__ == '__main__':
    MySQL_Init()
    # add_event('未涩涩', 1)
    # del_event(1)
    # check_data()
    # change_data(2, '2022-06-15', 1, '')
    close_DB()
